package Server;

import java.beans.XMLDecoder;
import java.beans.XMLEncoder;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.Hashtable;
import java.util.List;
import java.util.Vector;




import org.gibello.zql.ZExpression;
import org.gibello.zql.ZGroupBy;

public class QuanLyBang {
	List<MyTable> listTable;
	ServerGUI fatherFrame;

	public QuanLyBang() {
		listTable = new ArrayList<MyTable>();
	}

	public void setListTable(List<MyTable> listTable) {
		this.listTable = listTable;
	}

	public List<MyTable> layDanhSachCacBang() {
		return listTable;
	}

	public void themBang(MyTable myTable) {
		listTable.add(myTable);
	}

	public MyTable layBang(int viTri) {
		return listTable.get(viTri);
	}

	public void xoaBang(int viTri) {
		listTable.remove(viTri);
	}

	public List<String> layDanhSachBang() {
		List<String> ds = new ArrayList<String>();
		for (int i = 0; i < listTable.size(); i++) {
			ds.add(listTable.get(i).layTenBang());
		}
		return ds;
	}

	public void luuCSDL(String fileLocation) throws Exception {
		List<BangXML> listTableXML = new ArrayList<BangXML>();
		for (MyTable myTable : listTable) {
			BangXML tableXML = new BangXML(myTable);
			listTableXML.add(tableXML);
		}

		FileOutputStream os = new FileOutputStream(fileLocation);
		XMLEncoder encoder = new XMLEncoder(os);
		encoder.writeObject(listTableXML);
		encoder.close();
		os.close();
	}

	public void docCSDL(String fileLocation) throws Exception {
		FileInputStream is = new FileInputStream(fileLocation);
		XMLDecoder decoder = new XMLDecoder(is);
		List<BangXML> listTableXML = (ArrayList<BangXML>) decoder.readObject();
		decoder.close();
		is.close();
		listTable = new ArrayList<MyTable>();
		for (BangXML tableXML : listTableXML) {
			MyTable myTable = new MyTable(450, 100, tableXML.getData(),
					tableXML.getNames(), tableXML.getTypeData(),
					tableXML.getTableName(),tableXML.getKhoaChinh());
			listTable.add(myTable);
		}
	}

	/**
	 * Vẽ bảng vị trí viTriBangDuocChon trên khung fatherFrame
	 * 
	 * @param Khung frame Server
	 * @param Vị trí bảng được chọn
	 */
	public void veBang(ServerGUI _fatherFrame, int _viTriBangDuocChon) {
		fatherFrame = _fatherFrame;
		MyTable bangDuocChon = layBang(_viTriBangDuocChon);
		fatherFrame.getScrollHienBang().setViewportView(bangDuocChon);
		fatherFrame.getScrollHienBang().validate();
	}

	// selectSQL ko where
	public MyTable selectSQL(Vector select, Vector from) {
		if (from.size() == 1)// select từ 1 bảng
		{
			if (select.get(0).toString().equals("*"))
				for (MyTable myTable : listTable) {
					if (myTable.layTenBang().equalsIgnoreCase(
							from.get(0).toString()))
						return myTable;
				}
			else {
				int bangCanTim = timBang(from.get(0).toString());
				if (bangCanTim == -1)// không tim thấy bảng
					return null;

				int[] cotCanTim = new int[select.size()];

				for (int i = 0; i < cotCanTim.length; i++) {
					cotCanTim[i] = timCot(bangCanTim, select.get(i).toString());
					if (cotCanTim[i] == -1)
						return null;
				}

				List<String> tenCot = new ArrayList<String>();
				for (int i = 0; i < cotCanTim.length; i++) {
					tenCot.add(listTable.get(bangCanTim).layDanhSachTruongDuLieu()
							.get(cotCanTim[i]));
				}
				for (int i = 0; i < tenCot.size(); i++) {
					System.out.println(tenCot.get(i));
				}
				int soDongDuLieu = listTable.get(bangCanTim).laySoDong();
				List<Object[]> data = new ArrayList<Object[]>();

				Object[] temp = null;
				for (int i = 0; i < soDongDuLieu; i++) {
					temp = new Object[cotCanTim.length];
					for (int j = 0; j < cotCanTim.length; j++) {

						temp[j] = (listTable.get(bangCanTim).layMyModel()
								.getValueAt(i, cotCanTim[j]));
					}

					data.add(temp);
				}
				List<String> types = null;
				String name = "";
				List <Boolean> khoaChinh = null;
				MyTable myTable = new MyTable(450, 100, data, tenCot, types,name,khoaChinh);
				return myTable;
			}
		}

		return null;
	}

	// select co where
	public MyTable selectSQLCoWhere(Vector select, Vector from,ZExpression where)
	{
		if (from.size() == 1)// select từ 1 bảng
		{
			
			String where1 = null;
			String where2 = null;
			String where3 = null;
			String where4 = null;
			String operator1 = null;
			String operator2 = null;
			String operatorChinh = where.getOperator();
		
			Vector dieuKien = where.getOperands();
			String dieuKien1 = dieuKien.get(0).toString();
			if (la1MangDieuKien(dieuKien1))// là 1 vế điều kiện
			{
				ZExpression temp = (ZExpression) where.getOperand(0);
				Vector op = temp.getOperands();
				where1 = op.get(0).toString();				
				where2 = op.get(1).toString();
				operator1 = temp.getOperator();
			} 
			else
				where1 = dieuKien.get(0).toString();
			
			String dieuKien2 = dieuKien.get(1).toString();
			if (la1MangDieuKien(dieuKien2))// là 1 vế điều kiện
			{
				ZExpression temp = (ZExpression) where.getOperand(1);
				Vector op = temp.getOperands();
				where3 = op.get(0).toString();
				where4 = op.get(1).toString();
				operator2 = temp.getOperator();
			} else
			{
				where2 = dieuKien.get(1).toString();
				operator1 = where.getOperator();
			}
			if(where3 == null && where4 == null)// chỉ có 1 vế điều kiện
			{
				if (select.get(0).toString().equals("*")) 
				{
					int bangCanTim = timBang(from.get(0).toString());
					if (bangCanTim == -1)// không tim thấy bảng
						return null;
					List<String> tenCot = listTable.get(bangCanTim).layDanhSachTruongDuLieu();
					int cotDieuKien = timCot(bangCanTim, where1);
					if (cotDieuKien == -1)
						return null;
					List<Integer> dongDieuKien = timCacDong(bangCanTim,cotDieuKien, where2,operator1);
					if (dongDieuKien.size() == 0)
						return null;
					List<Object[]> data = new ArrayList<Object[]>();
					for (int i = 0; i < dongDieuKien.size(); i++) {
						Object[] temp = listTable.get(bangCanTim).layDanhSachDuLieu()
								.get(dongDieuKien.get(i));
						data.add(temp);
					}
	
					List<String> types = null;
					String name = "";
					List<Boolean> khoaChinh = null;
					MyTable myTable = new MyTable(450, 100, data, tenCot, types,
							name,khoaChinh);
					return myTable;
				} 
				
				else // select nhiều cột
				{
					int bangCanTim = timBang(from.get(0).toString());
					if (bangCanTim == -1)// không tim thấy bảng
						return null;
	
					int[] cotCanTim = new int[select.size()];
	
					for (int i = 0; i < cotCanTim.length; i++) {
						cotCanTim[i] = timCot(bangCanTim, select.get(i).toString());
						if (cotCanTim[i] == -1)
							return null;
					}
	
					List<String> tenCot = new ArrayList<String>();
					for (int i = 0; i < cotCanTim.length; i++) {
						tenCot.add(listTable.get(bangCanTim).layDanhSachTruongDuLieu()
								.get(cotCanTim[i]));
					}
				
	
					int cotDieuKien = timCot(bangCanTim, where1);
					List<Integer> dongDieuKien = timCacDong(bangCanTim,cotDieuKien, where2,operator1);
	
					List<Object[]> data = new ArrayList<Object[]>();
					Object[] temp = null;
					for (int j = 0; j < dongDieuKien.size(); j++) {
						temp = new Object[cotCanTim.length];
						for (int i = 0; i < temp.length; i++) {
	
							temp[i] = (listTable.get(bangCanTim).layMyModel()
									.getValueAt(dongDieuKien.get(j), cotCanTim[i]));
						}
						data.add(temp);
					}
					List<String> types = null;
					String name = "";
					List<Boolean> khoaChinh = null;
					MyTable myTable = new MyTable(450, 100, data, tenCot, types,
							name,khoaChinh);
					return myTable;
				}
			}
			else // 2 điều kiện
			{
				if (select.get(0).toString().equals("*")) 
				{
					int bangCanTim = timBang(from.get(0).toString());
					if (bangCanTim == -1)// không tim thấy bảng
						return null;
					List<String> tenCot = listTable.get(bangCanTim).layDanhSachTruongDuLieu();
					// điều kiện 1
					
					int cotDieuKien1 = timCot(bangCanTim, where1);
					if (cotDieuKien1 == -1)
						return null;
					List<Integer> dongDieuKien1 = timCacDong(bangCanTim,cotDieuKien1, where2,operator1);
					//điều kiện 2
					
					int cotDieuKien2 = timCot(bangCanTim, where3);
					if (cotDieuKien2 == -1)
						return null;
					List<Integer> dongDieuKien2 = timCacDong(bangCanTim,cotDieuKien2, where4,operator2);
					// tổng hợp 2 dòng điều kiện 
					List<Integer> dongDieuKien;
					if(operatorChinh.equalsIgnoreCase("or"))
						 dongDieuKien = ghepListOr(dongDieuKien1, dongDieuKien2);
					else
						dongDieuKien = ghepListAnd(dongDieuKien1, dongDieuKien2);
					if (dongDieuKien.size() == 0)
						return null;
					List<Object[]> data = new ArrayList<Object[]>();
					for (int i = 0; i < dongDieuKien.size(); i++) {
						Object[] temp = listTable.get(bangCanTim).layDanhSachDuLieu()
								.get(dongDieuKien.get(i));
						data.add(temp);
					}
	
					List<String> types = null;
					String name = "";
					List<Boolean> khoaChinh = null;
					MyTable myTable = new MyTable(450, 100, data, tenCot, types,
							name,khoaChinh);
					return myTable;
				} 
				
				else // select nhiều cột
				{
					int bangCanTim = timBang(from.get(0).toString());
					if (bangCanTim == -1)// không tim thấy bảng
						return null;
	
					int[] cotCanTim = new int[select.size()];
	
					for (int i = 0; i < cotCanTim.length; i++) {
						cotCanTim[i] = timCot(bangCanTim, select.get(i).toString());
						if (cotCanTim[i] == -1)
							return null;
					}
	
					List<String> tenCot = new ArrayList<String>();
					for (int i = 0; i < cotCanTim.length; i++) {
						tenCot.add(listTable.get(bangCanTim).layDanhSachTruongDuLieu()
								.get(cotCanTim[i]));
					}
				
	
					// điều kiện 1
					
					int cotDieuKien1 = timCot(bangCanTim, where1);
					if (cotDieuKien1 == -1)
						return null;
					List<Integer> dongDieuKien1 = timCacDong(bangCanTim,cotDieuKien1, where2,operator1);
					//điều kiện 2
					
					int cotDieuKien2 = timCot(bangCanTim, where3);
					if (cotDieuKien2 == -1)
						return null;
					List<Integer> dongDieuKien2 = timCacDong(bangCanTim,cotDieuKien2, where4,operator2);
					// tổng hợp 2 dòng điều kiện 
					List<Integer> dongDieuKien;
					if(operatorChinh.equalsIgnoreCase("or"))
						 dongDieuKien = ghepListOr(dongDieuKien1, dongDieuKien2);
					else
						dongDieuKien = ghepListAnd(dongDieuKien1, dongDieuKien2);
					System.out.println(dongDieuKien.size());
					if (dongDieuKien.size() == 0)
						return null;
					List<Object[]> data = new ArrayList<Object[]>();
					Object[] temp = null;
					for (int j = 0; j < dongDieuKien.size(); j++) {
						temp = new Object[cotCanTim.length];
						for (int i = 0; i < temp.length; i++) {
	
							temp[i] = (listTable.get(bangCanTim).layMyModel()
									.getValueAt(dongDieuKien.get(j), cotCanTim[i]));
						}
						data.add(temp);
					}
					List<String> types = null;
					String name = "";
					List<Boolean> khoaChinh = null;
					MyTable myTable = new MyTable(450, 100, data, tenCot, types,
							name,khoaChinh);
					return myTable;
				}
			}
		}
		if (from.size() == 2)// phép kết
		{

			/* lấy tên bảng và vị trí bảng từ from */
			String tenBang1_From = from.get(0).toString();
			String tenBang2_From = from.get(1).toString();
			String where1 = null;
			String where2 = null;
			String where3 = null;
			String where4 = null;
			String operator1 = null;
			String operator2 = null;
			String operatorChinh = null;
			
			Vector dieuKien = where.getOperands();
			String dieuKien1 = dieuKien.get(0).toString();
			if (la1MangDieuKien(dieuKien1))// là 1 vế điều kiện
			{
				ZExpression temp = (ZExpression) where.getOperand(0);
				Vector op = temp.getOperands();
				where1 = op.get(0).toString();				
				where2 = op.get(1).toString();
				operator1 = temp.getOperator();
			} 
			else
				where1 = dieuKien.get(0).toString();
			
			String dieuKien2 = dieuKien.get(1).toString();
			if (la1MangDieuKien(dieuKien2))// là 1 vế điều kiện
			{
				ZExpression temp = (ZExpression) where.getOperand(1);
				Vector op = temp.getOperands();
				where3 = op.get(0).toString();
				where4 = op.get(1).toString();
				operator2 = temp.getOperator();
			} else
				where2 = dieuKien.get(1).toString();
			
			if (where3 == null || where4 == null)// điều kiện chỉ có 1 vế
			{
				if(!where1.contains(".") || !where2.contains("."))
					return null;
				String tenBang1_Where = layTenBangTuWhere(where1);// lấy tên
																	// bảng từ
																	// where
				String tenBang2_Where = layTenBangTuWhere(where2);
				String tenCot1_Where = layTenCotTuWhere(where1);// lấy tên cột
																// từ where
				String tenCot2_Where = layTenCotTuWhere(where2);
				// so sanh tên bảng ở where và from
				if (!tenBang1_From.equalsIgnoreCase(tenBang1_Where)
						&& !tenBang1_From.equalsIgnoreCase(tenBang2_Where)
						|| !tenBang2_From.equalsIgnoreCase(tenBang1_Where)
						&& !tenBang2_From.equalsIgnoreCase(tenBang2_Where))// không
																			// khớp
																			// với
																			// nhau
					return null;
				int bang1 = timBang(tenBang1_Where);
				if (bang1 == -1)
					return null;
				int bang2 = timBang(tenBang2_Where);
				if (bang2 == -1)
					return null;

				int cotCanKet1 = timCot(bang1, tenCot1_Where);
				if (cotCanKet1 == -1)
					return null;
				int cotCanKet2 = timCot(bang2, tenCot2_Where);
				if (cotCanKet2 == -1)
					return null;
				
				
				List<String> tenCot2 = listTable.get(bang2).layDanhSachTruongDuLieu();
				List<String> tenCot1 = listTable.get(bang1).layDanhSachTruongDuLieu();
				// kết tên cột của 2 bảng
				List<String> tenCot =ghepList(tenCot1, tenCot2);
				// kết dữ liệu của 2 bảng
				List<Object[]> data = layDuLieuKet(bang1, bang2, cotCanKet1,
						cotCanKet2, tenCot2, tenCot1);
				System.out.println("so dong du lieu ket: "+data.size());
				
				// xóa bỏ cột trùng khi kết ra bảng mới
				int cotTrung = tenCot1.size() + cotCanKet2;
				tenCot.remove(cotTrung);
				System.out.println("so truong ket: "+tenCot.size());
				List<String> kieuDuLieu = ghepList(listTable.get(bang1).layDanhSachKieuDuLieu(),
						listTable.get(bang2).layDanhSachKieuDuLieu());
				kieuDuLieu.remove(cotTrung);
				List<Object[]> newData = xoaCotDuLieu(cotTrung, data);
				System.out.println("so dong du lieu ket moi: "+newData.size());
				// /////////////////////////////////////////////

				List<Boolean> khoaChinh = null;
				String name = "";
				MyTable bangKet = new MyTable(450, 100, newData, tenCot, kieuDuLieu,name,khoaChinh);
				
				if (select.get(0).toString().equals("*"))// select *
				{
					
					return bangKet;
				} 
				else
				{
					MyTable myTable = selectTuBangKet(select, bangKet);
					return myTable;
				}
			}
			else// 2 điều kiện
			{
				String tenBang1_Where = null;
				String tenBang2_Where = null;
				String tenBang3_Where = null;
				String tenBang4_Where = null;
				String tenCot1_Where = null;
				String tenCot2_Where = null;
				if (where2.contains(".")) // điều kiện kết nằm ở vế điều kiện đầu
				{
					tenBang1_Where = layTenBangTuWhere(where1);// lấy tên bảng
																// từ where
					tenBang2_Where = layTenBangTuWhere(where2);
					tenCot1_Where = layTenCotTuWhere(where1);// lấy tên cột từ
																// where
					tenCot2_Where = layTenCotTuWhere(where2);
					tenBang3_Where = where3;
					tenBang4_Where = where4;
					operatorChinh = operator2;
				}
				if (where4.contains(".")) //// điều kiện kết nằm ở vế điều kiện sau
				{
					tenBang1_Where = layTenBangTuWhere(where3);// lấy tên bảng
																// từ where
					tenBang2_Where = layTenBangTuWhere(where4);
					tenCot1_Where = layTenCotTuWhere(where3);// lấy tên cột từ
																// where
					tenCot2_Where = layTenCotTuWhere(where4);
					tenBang3_Where = where1;
					tenBang4_Where = where2;
					operatorChinh = operator1;
				}
				if (!tenBang1_From.equalsIgnoreCase(tenBang1_Where)
						&& !tenBang1_From.equalsIgnoreCase(tenBang2_Where)
						|| !tenBang2_From.equalsIgnoreCase(tenBang1_Where)
						&& !tenBang2_From.equalsIgnoreCase(tenBang2_Where))// không
																			// khớp
																			// với
																			// nhau
					return null;
				System.out.print(tenBang1_Where);
				int bang1 = timBang(tenBang1_Where);
				if (bang1 == -1)
					return null;
				int bang2 = timBang(tenBang2_Where);
				if (bang2 == -1)
					return null;

				int cotCanKet1 = timCot(bang1, tenCot1_Where);
				if (cotCanKet1 == -1)
					return null;
				int cotCanKet2 = timCot(bang2, tenCot2_Where);
				if (cotCanKet2 == -1)
					return null;
				List<String> tenCot2 = listTable.get(bang2).layDanhSachTruongDuLieu();
				List<String> tenCot1 = listTable.get(bang1).layDanhSachTruongDuLieu();
				// kết tên cột của 2 bảng
				List<String> tenCot = ghepList(tenCot1, tenCot2);
				// kết dữ liệu của 2 bảng
				List<Object[]> data = layDuLieuKet(bang1, bang2, cotCanKet1,
						cotCanKet2, tenCot2, tenCot1);
				// xóa bỏ cột trùng khi kết ra bảng mới
				int cotTrung = tenCot1.size() + cotCanKet2;
				tenCot.remove(cotTrung);
				List<String> kieuDuLieu = ghepList(listTable.get(bang1).layDanhSachKieuDuLieu(),
						listTable.get(bang2).layDanhSachKieuDuLieu());
				kieuDuLieu.remove(cotTrung);
				List<Object[]> newData = xoaCotDuLieu(cotTrung, data);
				// /////////////////////////////////////////////

				List<Boolean> khoaChinh = null;
				String name = "";
				MyTable bangKet = new MyTable(450, 100, newData, tenCot, kieuDuLieu,name,khoaChinh);
				return selectTuBangKet(select, bangKet, tenBang3_Where, tenBang4_Where,operatorChinh);
				
			}	

			
		}

		return null;
	}
	
	//select có group by
	public MyTable selectSQLCoGroupBy(Vector select,Vector from,ZGroupBy groupby)
	{
		if(from.size()==1)
		{
		Vector vGroup = groupby.getGroupBy();
		ZExpression having = (ZExpression)groupby.getHaving();
		String where1 = null;
		String where2 = null;
		String where3 = null;
		String where4 = null;
		String operatorChinh = having.getOperator();
		String operator1 = null;
		String operator2 = null;
		String hamYeuCau = null;
		String tenCotCanXet = null;
		Vector dieuKien = having.getOperands();
		
		String dieuKien1 = dieuKien.get(0).toString();
		if (la1MangDieuKien(dieuKien1))// là 1 vế điều kiện
		{
			ZExpression temp = (ZExpression) having.getOperand(0);
			Vector op = temp.getOperands();
			where1 = op.get(0).toString();				
			where2 = op.get(1).toString();
			operator1 = temp.getOperator();
		} 
		else
			where1 = dieuKien.get(0).toString();
		
		String dieuKien2 = dieuKien.get(1).toString();
		if (la1MangDieuKien(dieuKien2))// là 1 vế điều kiện
		{
			ZExpression temp = (ZExpression) having.getOperand(1);
			Vector op = temp.getOperands();
			where3 = op.get(0).toString();
			where4 = op.get(1).toString();
			operator2 = temp.getOperator();
		} else
		{
			where2 = dieuKien.get(1).toString();
			operator1 = having.getOperator();
		}
		if(where3 == null && where4 == null)// chỉ có 1 vế điều kiện
		{
			
				int bangCanTim = timBang(from.get(0).toString());
				if (bangCanTim == -1)// không tim thấy bảng
					return null;
				// add thêm cột cần group 
				int cotCanGroup = timCot(bangCanTim, vGroup.get(0).toString());
				if(cotCanGroup==-1) return null;
				///
				int[] cotCanTim = new int[select.size()+1];
				
				for (int i = 0; i < cotCanTim.length -1; i++)
				{
					if(select.get(i).toString().contains("("))
					{
						String temp = select.get(i).toString();
						hamYeuCau = layHamYeuCau(temp);
						
						tenCotCanXet = layTenCotCanXet(temp);
						
						cotCanTim[i] = timCot(bangCanTim, tenCotCanXet);
						if (cotCanTim[i] == -1)
							return null;
					}
					else
					{
						cotCanTim[i] = timCot(bangCanTim, select.get(i).toString());
						if (cotCanTim[i] == -1)
							return null;
					}
				}
			// cột cuối dùng để lưu cột group
				cotCanTim[cotCanTim.length-1]=cotCanGroup;
				
				
				List<String> tenCot = new ArrayList<String>();
				for (int i = 0; i < cotCanTim.length; i++) {
					tenCot.add(listTable.get(bangCanTim).layDanhSachTruongDuLieu().get(cotCanTim[i]));
				}
				
				int cotDieuKien = timCot(bangCanTim, where1);
				if (cotDieuKien == -1)
					return null;
				
				List<Integer> dongDieuKien = timCacDong(bangCanTim,cotDieuKien, where2,operator1);
				if (dongDieuKien.size() == 0)
					return null;
				
				List<Object[]> data = new ArrayList<Object[]>();
				Object[] temp = null;
				for (int j = 0; j < dongDieuKien.size(); j++) {
					temp = new Object[cotCanTim.length];
					for (int i = 0; i < temp.length; i++) {

						temp[i] = (listTable.get(bangCanTim).layMyModel()
								.getValueAt(dongDieuKien.get(j), cotCanTim[i]));
					}
					data.add(temp);
				}
				
				int cotCanXet = -1;
				
				for (int i = 0; i < tenCot.size(); i++) {
					if(tenCot.get(i).equals(tenCotCanXet))
						cotCanXet = i;
				}
				
				if(cotCanXet==-1)return null;
				if (!listTable.get(bangCanTim).layDanhSachKieuDuLieu().get(cotCanTim[cotCanXet]).equals("int"))
					return null;
				List<Integer> dongCanXoa = new ArrayList<Integer>();
				if(hamYeuCau.equalsIgnoreCase("max"))
				{
					for (int i = 0; i < data.size() -1; i++) {
						for (int j = i+1; j < data.size(); j++) 
						{
							if(data.get(i)[cotCanTim.length-1].toString().equals(data.get(j)[cotCanTim.length-1].toString()) )
								if(Integer.parseInt(data.get(i)[cotCanXet].toString()) 
										< Integer.parseInt(data.get(j)[cotCanXet].toString()))
								{
									
									data.get(i)[cotCanXet] = data.get(j)[cotCanXet];
									dongCanXoa.add(j);
								}
								else
									dongCanXoa.add(j);
						}
					}
				}
				if(hamYeuCau.equalsIgnoreCase("min"))
				{
					for (int i = 0; i < data.size() -1; i++) {
						for (int j = i+1; j < data.size(); j++) 
						{
							if(data.get(i)[cotCanTim.length-1].toString().equals(data.get(j)[cotCanTim.length-1].toString()) )
								if(Integer.parseInt(data.get(i)[cotCanXet].toString()) 
										> Integer.parseInt(data.get(j)[cotCanXet].toString()))
								{
									
									data.get(i)[cotCanXet] = data.get(j)[cotCanXet];
									dongCanXoa.add(j);
								}
								else
									dongCanXoa.add(j);
						}
					}
				}
				if(hamYeuCau.equalsIgnoreCase("sum"))
				{
					for (int i = 0; i < data.size() -1; i++) 
					{
						int tong = Integer.parseInt(data.get(i)[cotCanXet].toString());
						for (int j = i+1; j < data.size(); j++) 
						{
							if(data.get(i)[cotCanTim.length-1].toString().equals(data.get(j)[cotCanTim.length-1].toString()) )
								{
									tong += Integer.parseInt(data.get(j)[cotCanXet].toString());
									data.get(i)[cotCanXet] = String.valueOf(tong);
									dongCanXoa.add(j);
								}
								
						}
					}
				}
				if(hamYeuCau.equalsIgnoreCase("avg"))
				{
					for (int i = 0; i < data.size() -1; i++) 
					{
						int tong = Integer.parseInt(data.get(i)[cotCanXet].toString());
						int count = 1;
						for (int j = i+1; j < data.size(); j++) 
						{
							if(data.get(i)[cotCanTim.length-1].toString().equals(data.get(j)[cotCanTim.length-1].toString()) )
								{
									tong += Integer.parseInt(data.get(j)[cotCanXet].toString());
									count ++;
									dongCanXoa.add(j);
								}
								
						}
						tong = tong / count;
						data.get(i)[cotCanXet] = String.valueOf(tong);
					}
				}
				if(hamYeuCau.equalsIgnoreCase("count"))
				{
					for (int i = 0; i < data.size() ; i++) 
					{
						int count = 1;
						for (int j = i+1; j < data.size(); j++) 
						{
							if(data.get(i)[cotCanTim.length-1].toString().equals(data.get(j)[cotCanTim.length-1].toString()) )
								{
						
									count ++;
									dongCanXoa.add(j);
								}
								
						}						
						data.get(i)[cotCanXet] = String.valueOf(count);
					}
				}
				List<Object[]> newData = new ArrayList<Object[]>();
				for (int i = 0; i < data.size(); i++) {
					if(!kiemTraPhanTuThuocMang(i, dongCanXoa))
						newData.add(data.get(i));
				}
				tenCot.remove(tenCot.size()-1);// xóa cột group
				
				List<String> types = null;
				String name = "";
				List<Boolean> khoaChinh = null;
				MyTable myTable = new MyTable(450, 100, newData, tenCot, types,
						name,khoaChinh);
				return myTable;
		}
		
			else // 2 điều kiện
			{
				int bangCanTim = timBang(from.get(0).toString());
				if (bangCanTim == -1)// không tim thấy bảng
					return null;
				// add thêm cột cần group 
				int cotCanGroup = timCot(bangCanTim, vGroup.get(0).toString());
				if(cotCanGroup==-1) return null;
				///
				int[] cotCanTim = new int[select.size()+1];
				
				for (int i = 0; i < cotCanTim.length-1; i++)
				{
					if(select.get(i).toString().contains("("))
					{
						String temp = select.get(i).toString();
						hamYeuCau = layHamYeuCau(temp);
						
						tenCotCanXet = layTenCotCanXet(temp);
						
						cotCanTim[i] = timCot(bangCanTim, tenCotCanXet);
						if (cotCanTim[i] == -1)
							return null;
					}
					else
					{
						cotCanTim[i] = timCot(bangCanTim, select.get(i).toString());
						if (cotCanTim[i] == -1)
							return null;
					}
				}
				cotCanTim[cotCanTim.length-1] = cotCanGroup;
				List<String> tenCot = new ArrayList<String>();
				for (int i = 0; i < cotCanTim.length; i++) {
					tenCot.add(listTable.get(bangCanTim).layDanhSachTruongDuLieu().get(cotCanTim[i]));
				}
				
			
				
	
					// điều kiện 1
					
					int cotDieuKien1 = timCot(bangCanTim, where1);
					if (cotDieuKien1 == -1)
						return null;
					List<Integer> dongDieuKien1 = timCacDong(bangCanTim,cotDieuKien1, where2,operator1);
					//điều kiện 2
					
					int cotDieuKien2 = timCot(bangCanTim, where3);
					if (cotDieuKien2 == -1)
						return null;
					List<Integer> dongDieuKien2 = timCacDong(bangCanTim,cotDieuKien2, where4,operator2);
					// tổng hợp 2 dòng điều kiện 
					List<Integer> dongDieuKien;
					if(operatorChinh.equalsIgnoreCase("or"))
						 dongDieuKien = ghepListOr(dongDieuKien1, dongDieuKien2);
					else
						dongDieuKien = ghepListAnd(dongDieuKien1, dongDieuKien2);
					
					if (dongDieuKien.size() == 0)
						return null;
					List<Object[]> data = new ArrayList<Object[]>();
					Object[] temp = null;
					for (int j = 0; j < dongDieuKien.size(); j++) {
						temp = new Object[cotCanTim.length];
						for (int i = 0; i < temp.length; i++) {
	
							temp[i] = (listTable.get(bangCanTim).layMyModel()
									.getValueAt(dongDieuKien.get(j), cotCanTim[i]));
						}
						data.add(temp);
					}
					
					
					int cotCanXet = -1;
		
					for (int i = 0; i < tenCot.size(); i++) {
						if(tenCot.get(i).equals(tenCotCanXet))
							cotCanXet = i;
					}
					
					if(cotCanXet==-1)return null;
					if (!listTable.get(bangCanTim).layDanhSachKieuDuLieu().get(cotCanTim[cotCanXet]).equals("int"))
						return null;
					List<Integer> dongCanXoa = new ArrayList<Integer>();
					if(hamYeuCau.equalsIgnoreCase("max"))
					{
						for (int i = 0; i < data.size() -1; i++) {
							for (int j = i+1; j < data.size(); j++) 
							{
								if(data.get(i)[cotCanTim.length-1].toString().equals(data.get(j)[cotCanTim.length-1].toString()) )
									if(Integer.parseInt(data.get(i)[cotCanXet].toString()) 
											< Integer.parseInt(data.get(j)[cotCanXet].toString()))
									{
										
										data.get(i)[cotCanXet] = data.get(j)[cotCanXet];
										dongCanXoa.add(j);
									}
									else
										dongCanXoa.add(j);
							}
						}
					}
					if(hamYeuCau.equalsIgnoreCase("min"))
					{
						for (int i = 0; i < data.size() -1; i++) {
							for (int j = i+1; j < data.size(); j++) 
							{
								if(data.get(i)[cotCanTim.length-1].toString().equals(data.get(j)[cotCanTim.length-1].toString()) )
									if(Integer.parseInt(data.get(i)[cotCanXet].toString()) 
											> Integer.parseInt(data.get(j)[cotCanXet].toString()))
									{
										
										data.get(i)[cotCanXet] = data.get(j)[cotCanXet];
										dongCanXoa.add(j);
									}
									else
										dongCanXoa.add(j);
							}
						}
					}
					if(hamYeuCau.equalsIgnoreCase("sum"))
					{
						for (int i = 0; i < data.size() -1; i++) 
						{
							int tong = Integer.parseInt(data.get(i)[cotCanXet].toString());
							for (int j = i+1; j < data.size(); j++) 
							{
								if(data.get(i)[cotCanTim.length-1].toString().equals(data.get(j)[cotCanTim.length-1].toString()) )
									{
										tong += Integer.parseInt(data.get(j)[cotCanXet].toString());
										data.get(i)[cotCanXet] = String.valueOf(tong);
										dongCanXoa.add(j);
									}
									
							}
						}
					}
					if(hamYeuCau.equalsIgnoreCase("avg"))
					{
						for (int i = 0; i < data.size() -1; i++) 
						{
							int tong = Integer.parseInt(data.get(i)[cotCanXet].toString());
							int count = 1;
							for (int j = i+1; j < data.size(); j++) 
							{
								if(data.get(i)[cotCanTim.length-1].toString().equals(data.get(j)[cotCanTim.length-1].toString()) )
									{
										tong += Integer.parseInt(data.get(j)[cotCanXet].toString());
										count ++;
										dongCanXoa.add(j);
									}
									
							}
							tong = tong / count;
							data.get(i)[cotCanXet] = String.valueOf(tong);
						}
					}
					if(hamYeuCau.equalsIgnoreCase("count"))
					{
						for (int i = 0; i < data.size() ; i++) 
						{
							int count = 1;
							for (int j = i+1; j < data.size(); j++) 
							{
								if(data.get(i)[cotCanTim.length-1].toString().equals(data.get(j)[cotCanTim.length-1].toString()) )
									{
							
										count ++;
										dongCanXoa.add(j);
									}
									
							}						
							data.get(i)[cotCanXet] = String.valueOf(count);
						}
					}
					
					List<Object[]> newData = new ArrayList<Object[]>();
					for (int i = 0; i < data.size(); i++) {
						if(!kiemTraPhanTuThuocMang(i, dongCanXoa))
							newData.add(data.get(i));
					}
					tenCot.remove(tenCot.size()-1);// xóa cột group
					List<String> types = null;
					String name = "";
					List<Boolean> khoaChinh = null;
					MyTable myTable = new MyTable(450, 100, newData, tenCot, types,
							name,khoaChinh);
					return myTable;
				
			}
		}
		return null;
	}
	public List<Object[]> layDuLieuKet(int bang1, int bang2, int cotCanKet1,
			int cotCanKet2, List<String> tenCot2, List<String> tenCot1)
	{
		List<Object[]> data = new ArrayList<Object[]>();
		int soDongBang1 = listTable.get(bang1).laySoDong();// lấy số dòng trong
															// bảng
		int soDongBang2 = listTable.get(bang2).laySoDong();
		Object[] temp = null;
		for (int i = 0; i < soDongBang1; i++) {
			temp = new Object[tenCot1.size() + tenCot2.size()];
			for (int j = 0; j < soDongBang2; j++) {
				if (listTable.get(bang1).layMyModel().getValueAt(i, cotCanKet1)
						.equals(listTable.get(bang2).layMyModel().getValueAt(j, cotCanKet2))) {
					for (int z1 = 0; z1 < tenCot1.size(); z1++) {
						temp[z1] = listTable.get(bang1).layMyModel()
								.getValueAt(i, z1);
					}
					for (int z2 = tenCot1.size(); z2 < tenCot1.size()
							+ tenCot2.size(); z2++) {
						temp[z2] = listTable.get(bang2).layMyModel()
								.getValueAt(j, z2 - tenCot1.size());
					}
				}
			}

			if (temp[cotCanKet1] != null)// dòng dữ liệu không rỗng
				data.add(temp);
		}
		return data;
	}

	// delete from [table] where [exp]
	public boolean deleteSQL(String nameTable, ZExpression where)
	{
		String where1 = null;
		String where2 = null;
		String where3 = null;
		String where4 = null;
		String operator1 = null;
		String operator2 = null;
		String operatorChinh = where.getOperator();
	
		Vector dieuKien = where.getOperands();
		String dieuKien1 = dieuKien.get(0).toString();
		if (la1MangDieuKien(dieuKien1))// là 1 vế điều kiện
		{
			ZExpression temp = (ZExpression) where.getOperand(0);
			Vector op = temp.getOperands();
			where1 = op.get(0).toString();				
			where2 = op.get(1).toString();
			operator1 = temp.getOperator();
		} 
		else
			where1 = dieuKien.get(0).toString();
		
		String dieuKien2 = dieuKien.get(1).toString();
		if (la1MangDieuKien(dieuKien2))// là 1 vế điều kiện
		{
			ZExpression temp = (ZExpression) where.getOperand(1);
			Vector op = temp.getOperands();
			where3 = op.get(0).toString();
			where4 = op.get(1).toString();
			operator2 = temp.getOperator();
		} else
		{
			where2 = dieuKien.get(1).toString();
			operator1 = where.getOperator();
		}
		
		int bangCanTim = timBang(nameTable);
		if (bangCanTim == -1)// không tim thấy bảng
			return false;
		List<Integer> dongDieuKien;
		if(where3 == null && where4 == null)// chỉ có 1 vế điều kiện
		{
				
				
				int cotDieuKien = timCot(bangCanTim, where1);
				if (cotDieuKien == -1)
					return false;
				dongDieuKien = timCacDong(bangCanTim,cotDieuKien, where2,operator1);
				if (dongDieuKien.size() == 0)
					return false;
						
			
		}
		else // có 2 vế điều kiện
		{
			
			
			// điều kiện 1
			
			int cotDieuKien1 = timCot(bangCanTim, where1);
			if (cotDieuKien1 == -1)
				return false;
			List<Integer> dongDieuKien1 = timCacDong(bangCanTim,cotDieuKien1, where2,operator1);
			//điều kiện 2
			
			int cotDieuKien2 = timCot(bangCanTim, where3);
			if (cotDieuKien2 == -1)
				return false;
			List<Integer> dongDieuKien2 = timCacDong(bangCanTim,cotDieuKien2, where4,operator2);
			// tổng hợp 2 dòng điều kiện 
			
			if(operatorChinh.equalsIgnoreCase("or"))
				 dongDieuKien = ghepListOr(dongDieuKien1, dongDieuKien2);
			else
				dongDieuKien = ghepListAnd(dongDieuKien1, dongDieuKien2);
			
		}
		if (dongDieuKien.size()==0)
			return false;
		else
			for (int i = 0; i < dongDieuKien.size(); i++) {
				listTable.get(bangCanTim).xoaDong(dongDieuKien.get(i));
				for (int j = i+1; j < dongDieuKien.size(); j++)
				{
					dongDieuKien.set(j, dongDieuKien.get(j) - 1) ; 
				}
			}
		return true;
			
	}

	// insert into [table] values [value]
	public boolean insertSQL(String nameTable, Vector value) {
		int bangCanTim = 0;
		bangCanTim = timBang(nameTable);
		if (bangCanTim != -1) {
			int soCot = listTable.get(bangCanTim).laySoCot();
			if (soCot == value.size()) 
			{
				String temp;
				Object[] newRow = new Object[soCot];
				for (int i = 0; i < newRow.length; i++) {
					if(kiemTraKieuDuLieu(value.get(i).toString(), listTable.get(bangCanTim).layDanhSachKieuDuLieu().get(i))!= -1)
					{
						temp = layNoiDungChuoi(value.get(i).toString());
						newRow[i] = temp;
					}
					else return false;
				}
				listTable.get(bangCanTim).themDong(newRow);
				return true;
			} else
				return false;
		} else
			return false;
	}

	// update [table] set [column=value] where [exp]
	public boolean updateSQL(String nameTable, Hashtable hashTable, ZExpression where) {
		String where1 = null;
		String where2 = null;
		String where3 = null;
		String where4 = null;
		String operator1 = null;
		String operator2 = null;
		String operatorChinh = where.getOperator();
	
		Vector dieuKien = where.getOperands();
		String dieuKien1 = dieuKien.get(0).toString();
		if (la1MangDieuKien(dieuKien1))// là 1 vế điều kiện
		{
			ZExpression temp = (ZExpression) where.getOperand(0);
			Vector op = temp.getOperands();
			where1 = op.get(0).toString();				
			where2 = op.get(1).toString();
			operator1 = temp.getOperator();
		} 
		else
			where1 = dieuKien.get(0).toString();
		
		String dieuKien2 = dieuKien.get(1).toString();
		if (la1MangDieuKien(dieuKien2))// là 1 vế điều kiện
		{
			ZExpression temp = (ZExpression) where.getOperand(1);
			Vector op = temp.getOperands();
			where3 = op.get(0).toString();
			where4 = op.get(1).toString();
			operator2 = temp.getOperator();
		} else
		{
			where2 = dieuKien.get(1).toString();
			operator1 = where.getOperator();
		}
		
		int bangCanTim = timBang(nameTable);
		if (bangCanTim == -1)// không tim thấy bảng
			return false;
		List<Integer> dongDieuKien;
		if(where3 == null && where4 == null)// chỉ có 1 vế điều kiện
		{
				
				
				int cotDieuKien = timCot(bangCanTim, where1);
				if (cotDieuKien == -1)
					return false;
				dongDieuKien = timCacDong(bangCanTim,cotDieuKien, where2,operator1);
				if (dongDieuKien.size() == 0)
					return false;
						
			
		}
		else // có 2 vế điều kiện
		{
			// điều kiện 1
			
			int cotDieuKien1 = timCot(bangCanTim, where1);
			if (cotDieuKien1 == -1)
				return false;
			List<Integer> dongDieuKien1 = timCacDong(bangCanTim,cotDieuKien1, where2,operator1);
			//điều kiện 2
			
			int cotDieuKien2 = timCot(bangCanTim, where3);
			if (cotDieuKien2 == -1)
				return false;
			List<Integer> dongDieuKien2 = timCacDong(bangCanTim,cotDieuKien2, where4,operator2);
			// tổng hợp 2 dòng điều kiện 
			
			if(operatorChinh.equalsIgnoreCase("or"))
				 dongDieuKien = ghepListOr(dongDieuKien1, dongDieuKien2);
			else
				dongDieuKien = ghepListAnd(dongDieuKien1, dongDieuKien2);
			
		}
		if (dongDieuKien.size()==0)
			return false;
		
		int cotCanSua = 0;
		cotCanSua = timCotCanSua(bangCanTim, hashTable);
		System.out.println(cotCanSua);
		if (cotCanSua == -1) // không tìm được cột cần sửa
			return false;
		else
			suaOThoaDieuKien(bangCanTim, cotCanSua, dongDieuKien, hashTable);
		return true;
	}

	public int timBang(String name)// trả về index của bảng trong list với tên
									// cần tìm
	{
		for (int i = 0; i < listTable.size(); i++)
			if (listTable.get(i).layTenBang().equalsIgnoreCase(name))
				return i;
		return -1;
	}

	public int timCot(int bangCanTim, String name) // trả về index của cột trong
													// list với tên cần tìm
	{
		List<String> tempColumnNames = listTable.get(bangCanTim).layDanhSachTruongDuLieu();
		for (int i = 0; i < tempColumnNames.size(); i++)
			// lấy tên cột được đặt điều kiện
			if (tempColumnNames.get(i).equalsIgnoreCase(name))
				return i;
		return -1;
	}

	public int timCot(MyTable table, String name) // trả về index của cột trong
	// list với tên cần tìm
	{
		List<String> tempColumnNames = table.layDanhSachTruongDuLieu();
		for (int i = 0; i < tempColumnNames.size(); i++)
			// lấy tên cột được đặt điều kiện
			if (tempColumnNames.get(i).equalsIgnoreCase(name))
				return i;
		return -1;
	}

	public int timDong(int bangCanTim, int cotCanTim, String name) {
		for (int i = 0; i < listTable.get(bangCanTim).laySoDong(); i++)
			// tìm dòng thỏa điều kiện
			if (listTable.get(bangCanTim).layMyModel().getValueAt(i, cotCanTim)
					.equals(name))
				return i;
		return -1;
	}

	public List<Integer> timCacDong(int bangCanTim, int cotCanTim, String name) {
		List<Integer> cacDongThoa = new ArrayList<Integer>();
		String temp;
		for (int i = 0; i < listTable.get(bangCanTim).laySoDong(); i++)
		{
			// tìm dòng thỏa điều kiện
			if(kiemTraKieuDuLieu(name, listTable.get(bangCanTim).layDanhSachKieuDuLieu().get(cotCanTim)) != -1)
			{
				temp = layNoiDungChuoi(name);
				if (listTable.get(bangCanTim).layMyModel().getValueAt(i, cotCanTim)
						.equals(temp))
					cacDongThoa.add(i);
			}
		}
		return cacDongThoa;
	}
	public List<Integer> timCacDong(int bangCanTim, int cotCanTim, String value,String dieuKien) {
		List<Integer> cacDongThoa = new ArrayList<Integer>();
		
		if(kiemTraKieuDuLieu(value, listTable.get(bangCanTim).layDanhSachKieuDuLieu().get(cotCanTim))== 1)// dữ liệu string
		{
			String temp;
			for (int i = 0; i < listTable.get(bangCanTim).laySoDong(); i++)
			{
				// tìm dòng thỏa điều kiện		
					if(dieuKien.equals("=")  )
					{
						temp = layNoiDungChuoi(value);				
						if (listTable.get(bangCanTim).layMyModel().getValueAt(i, cotCanTim).equals(temp))
							cacDongThoa.add(i);	
					}
			}
		}
		if(kiemTraKieuDuLieu(value, listTable.get(bangCanTim).layDanhSachKieuDuLieu().get(cotCanTim))== 2)// dữ liệu int
		{
			int temp = Integer.parseInt(value);
			if(dieuKien.equals("="))
			{
				for (int i = 0; i < listTable.get(bangCanTim).laySoDong(); i++)
				{	
					if (Integer.parseInt(listTable.get(bangCanTim).layMyModel().getValueAt(i, cotCanTim).toString()) == temp)
						cacDongThoa.add(i);		
				}
			}
			if(dieuKien.equals(">=") )
			{
				for (int i = 0; i < listTable.get(bangCanTim).laySoDong(); i++)
				{
					if (Integer.parseInt(listTable.get(bangCanTim).layMyModel().getValueAt(i, cotCanTim).toString()) >= temp)
						cacDongThoa.add(i);		
				}	
			}
			if(dieuKien.equals("<=") )
			{
				for (int i = 0; i < listTable.get(bangCanTim).laySoDong(); i++)
				{
					if (Integer.parseInt(listTable.get(bangCanTim).layMyModel().getValueAt(i, cotCanTim).toString()) <= temp)
						cacDongThoa.add(i);		
				}	
			}
			if(dieuKien.equals(">") )
			{
				for (int i = 0; i < listTable.get(bangCanTim).laySoDong(); i++)
				{
					if (Integer.parseInt(listTable.get(bangCanTim).layMyModel().getValueAt(i, cotCanTim).toString()) > temp)
						cacDongThoa.add(i);		
				}	
			}
			if(dieuKien.equals("<") )
			{
				for (int i = 0; i < listTable.get(bangCanTim).laySoDong(); i++)
				{
					if (Integer.parseInt(listTable.get(bangCanTim).layMyModel().getValueAt(i, cotCanTim).toString()) < temp)
						cacDongThoa.add(i);		
				}	
			}
		}
		return cacDongThoa;
	
	}
	public List<Integer> timCacDong(MyTable bangKet, int cotCanTim, String name) {
		List<Integer> cacDongThoa = new ArrayList<Integer>();
		String temp;
		for (int i = 0; i < bangKet.laySoDong(); i++)
		{
			// tìm dòng thỏa điều kiện
			if(kiemTraKieuDuLieu(name, bangKet.layDanhSachKieuDuLieu().get(cotCanTim))!= -1)
			{
				temp = layNoiDungChuoi(name);
				if (bangKet.layMyModel().getValueAt(i, cotCanTim).equals(temp))
					cacDongThoa.add(i);
			}
		}
		return cacDongThoa;
	}
	public List<Integer> timCacDong(MyTable bangKet, int cotCanTim, String value,String dieuKien) {
		List<Integer> cacDongThoa = new ArrayList<Integer>();
	
		if(kiemTraKieuDuLieu(value, bangKet.layDanhSachKieuDuLieu().get(cotCanTim))== 1)// dữ liệu string
		{
			String temp;
			for (int i = 0; i < bangKet.laySoDong(); i++)
			{
				// tìm dòng thỏa điều kiện		
					if(dieuKien.equals("=")  )
					{
						temp = layNoiDungChuoi(value);				
						if (bangKet.layMyModel().getValueAt(i, cotCanTim).equals(temp))
							cacDongThoa.add(i);	
					}
			}
		}
		if(kiemTraKieuDuLieu(value, bangKet.layDanhSachKieuDuLieu().get(cotCanTim))== 2)// dữ liệu int
		{
			int temp = Integer.parseInt(value);
			if(dieuKien.equals("="))
			{
				for (int i = 0; i < bangKet.laySoDong(); i++)
				{	
					if (Integer.parseInt(bangKet.layMyModel().getValueAt(i, cotCanTim).toString()) == temp)
						cacDongThoa.add(i);		
				}
			}
			if(dieuKien.equals(">=") )
			{
				for (int i = 0; i < bangKet.laySoDong(); i++)
				{
					if (Integer.parseInt(bangKet.layMyModel().getValueAt(i, cotCanTim).toString()) >= temp)
						cacDongThoa.add(i);		
				}	
			}
			if(dieuKien.equals("<=") )
			{
				for (int i = 0; i < bangKet.laySoDong(); i++)
				{
					if (Integer.parseInt(bangKet.layMyModel().getValueAt(i, cotCanTim).toString()) <= temp)
						cacDongThoa.add(i);		
				}	
			}
			if(dieuKien.equals(">") )
			{
				for (int i = 0; i < bangKet.laySoDong(); i++)
				{
					if (Integer.parseInt(bangKet.layMyModel().getValueAt(i, cotCanTim).toString()) > temp)
						cacDongThoa.add(i);		
				}	
			}
			if(dieuKien.equals("<") )
			{
				for (int i = 0; i < bangKet.laySoDong(); i++)
				{
					if (Integer.parseInt(bangKet.layMyModel().getValueAt(i, cotCanTim).toString()) < temp)
						cacDongThoa.add(i);		
				}	
			}
		}
		return cacDongThoa;
	}

	public int timCotCanSua(int bangCanTim, Hashtable hashTable) {
		List<String> tempColumnNames = listTable.get(bangCanTim).layDanhSachTruongDuLieu();
		for (int i = 0; i < tempColumnNames.size(); i++)
			if (hashTable.containsKey(tempColumnNames.get(i)))
				return i;
		return -1;
	}

	public void suaOThoaDieuKien(int bangCanTim, int cotCanSua, List<Integer> dongDieuKien,
			Hashtable hashTable) {
		List<String> tempColumnNames = listTable.get(bangCanTim).layDanhSachTruongDuLieu();
		Object thayThe = hashTable.get(tempColumnNames.get(cotCanSua));
		System.out.println(thayThe.toString());
		Object temp = null;
		if(kiemTraKieuDuLieu(thayThe.toString(), listTable.get(bangCanTim).
				layDanhSachKieuDuLieu().get(cotCanSua))==1)
		{
			temp = layNoiDungChuoi(thayThe.toString());
			for (int i = 0; i < dongDieuKien.size(); i++) 
			{		
				listTable.get(bangCanTim).layMyModel().setValueAt(temp,dongDieuKien.get(i), cotCanSua);
			}
		}
		if(kiemTraKieuDuLieu(thayThe.toString(), listTable.get(bangCanTim).
				layDanhSachKieuDuLieu().get(cotCanSua))==2)
		{
			temp = Integer.parseInt(thayThe.toString());
			for (int i = 0; i < dongDieuKien.size(); i++) 
			{		
				listTable.get(bangCanTim).layMyModel().setValueAt(temp,dongDieuKien.get(i), cotCanSua);
			}
		}
		
		
		
	}

	public List<Object[]> xoaCotDuLieu(int viTri, List<Object[]> data) {
		Object[] temp1 = null;
		Object[] temp2 = null;
		int j;
		List<Object[]> newData = new ArrayList<Object[]>();
		for (int t = 0; t < data.size(); t++) {
			j = 0;
			temp1 = data.get(t);
			temp2 = new Object[temp1.length - 1];
			for (int i = 0; i < temp1.length; i++) {
				if (i != viTri) {
					temp2[j] = temp1[i];
					j++;
				}
			}
			newData.add(temp2);
		}
		return newData;
	}

	public String layTenBangTuWhere(String where) {
		int dauCham = where.indexOf('.');
		String tenBang = where.substring(0, dauCham);
		return tenBang;
	}

	public String layTenCotTuWhere(String where) {
		int dauCham = where.indexOf('.');
		String tenCot = where.substring(dauCham + 1);
		return tenCot;
	}

	public MyTable selectTuBangKet(Vector select, MyTable bangKet) {

		int[] cotCanTim = new int[select.size()];

		for (int i = 0; i < cotCanTim.length; i++) {
			cotCanTim[i] = timCot(bangKet, select.get(i).toString());
			if (cotCanTim[i] == -1)
				return null;
		}

		List<String> tenCot = new ArrayList<String>();
		for (int i = 0; i < cotCanTim.length; i++) {

			tenCot.add(bangKet.layDanhSachTruongDuLieu().get(cotCanTim[i]));

		}
		for (int i = 0; i < tenCot.size(); i++) {
			System.out.println(tenCot.get(i));
		}
		int soDongDuLieu = bangKet.laySoDong();
		List<Object[]> data = new ArrayList<Object[]>();

		Object[] temp = null;
		for (int i = 0; i < soDongDuLieu; i++) {
			temp = new Object[cotCanTim.length];
			for (int j = 0; j < cotCanTim.length; j++) {

				temp[j] = (bangKet.layMyModel().getValueAt(i, cotCanTim[j]));
			}

			data.add(temp);
		}
		List<String> types = null;
		String name = "";
		List<Boolean> khoaChinh = null;
		MyTable myTable = new MyTable(450, 100, data, tenCot, types, name,khoaChinh);
		return myTable;
	}
	public MyTable selectTuBangKet(Vector select, MyTable bangKet,String veTrai,String vePhai,String dieuKien) 
	{
		
		if (select.get(0).toString().equals("*")) {
			List<String> tenCot = bangKet.layDanhSachTruongDuLieu();
			int cotDieuKien = timCot(bangKet ,veTrai);
			if (cotDieuKien == -1)
				return null;
			List<Integer> dongDieuKien = timCacDong(bangKet,
					cotDieuKien, vePhai,dieuKien);
			if (dongDieuKien.size() == 0)
				return null;
			List<Object[]> data = new ArrayList<Object[]>();
			for (int i = 0; i < dongDieuKien.size(); i++) {
				Object[] temp = bangKet.layDanhSachDuLieu()
						.get(dongDieuKien.get(i));
				data.add(temp);
			}

			List<String> types = null;
			String name = "";
			List<Boolean> khoaChinh = null;
			MyTable myTable = new MyTable(450, 100, data, tenCot, types,
					name,khoaChinh);
			return myTable;
		}
		else {
			

			int[] cotCanTim = new int[select.size()];

			for (int i = 0; i < cotCanTim.length; i++) {
				cotCanTim[i] = timCot(bangKet, select.get(i).toString());
				if (cotCanTim[i] == -1)
					return null;
			}

			List<String> tenCot = new ArrayList<String>();
			for (int i = 0; i < cotCanTim.length; i++) {
				tenCot.add(bangKet.layDanhSachTruongDuLieu()
						.get(cotCanTim[i]));
			}
			for (int i = 0; i < tenCot.size(); i++) {
				System.out.println(tenCot.get(i));
			}

			int cotDieuKien = timCot(bangKet, veTrai);
			List<Integer> dongDieuKien = timCacDong(bangKet,
					cotDieuKien, vePhai,dieuKien);

			List<Object[]> data = new ArrayList<Object[]>();
			Object[] temp = null;
			for (int j = 0; j < dongDieuKien.size(); j++) {
				temp = new Object[cotCanTim.length];
				for (int i = 0; i < temp.length; i++) {

					temp[i] = (bangKet.layMyModel()
							.getValueAt(dongDieuKien.get(j), cotCanTim[i]));
				}
				data.add(temp);
			}
			List<String> types = null;
			String name = "";
			List<Boolean> khoaChinh = null;
			MyTable myTable = new MyTable(450, 100, data, tenCot, types,
					name,khoaChinh);
			return myTable;
		}
		
		
	}
	public int kiemTraKieuDuLieu (String duLieuCanXet, String kieuDuLieu)
	{
		if((duLieuCanXet.contains("\'") || duLieuCanXet.contains("\"")) && kieuDuLieu.equals("char"))
			return 1;
		if(duLieuCanXet.matches("[+-]?\\d*(\\.\\d+)?") && kieuDuLieu.equals("int"))
			return 2;
		return -1;
			
	}
	public String layNoiDungChuoi (String chuoi)
	{
		if(chuoi.contains("\'") || chuoi.contains("\""))
			chuoi = chuoi.substring(1,chuoi.length()-1);
		return chuoi;
	}
	public List<String> ghepList (List<String> a, List<String> b)
	{
		List<String> data = new ArrayList<String>();
		for (String string : a) {
			data.add(string);
		}
		for (String string : b) {
			data.add(string);
		}
		return data;
	}
	public List<Integer> ghepListOr (List<Integer> a, List<Integer> b)
	{
		List<Integer> data = new ArrayList<Integer>();
			
		for (Integer i : a) {
			data.add(i);
		}
		for (Integer i : b) {
			if(!data.contains(i))
				data.add(i);
		}
		return data;
	}
	public List<Integer> ghepListAnd (List<Integer> a, List<Integer> b)
	{
		List<Integer> data = new ArrayList<Integer>();
			
		for (Integer i : a) {
			if(b.contains(i))
				data.add(i);
		}
		
		return data;
	}
	public boolean la1MangDieuKien (String dieuKien)
	{
		if(dieuKien.contains(">=") ||dieuKien.contains("<=")
				|| dieuKien.contains(">") ||dieuKien.contains("<") ||dieuKien.contains("="))
			return true;
		return false;
	}
	public String layHamYeuCau (String select)
	{
		String yeuCau;
		if(select.toLowerCase().contains("count"))
			yeuCau = select.substring(0,5);
		else 
			yeuCau = select.substring(0,3);
		return yeuCau;		
	}
	public String layTenCotCanXet (String select)
	{	
		String cotCanXet;
		if(select.toLowerCase().contains("count"))
			cotCanXet = select.substring(6,select.length()-1);
		else
			cotCanXet = select.substring(4,select.length()-1);
		return cotCanXet;
	}
	public boolean kiemTraPhanTuThuocMang (int index,List<Integer> listCanXet)
	{
		for (int i = 0; i < listCanXet.size(); i++) 
		{
			if (listCanXet.get(i)== index)
				return true;
		}
		return false;
	}
}
